﻿Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.Data.SqlClient

Public Class frmAD_Import

    Public tableExcel As New DataTable
    Private Sub ImportsExcel(ByVal str As String, ByVal dgv As DataGridView)
        dgv.Rows.Clear()
        Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + str + ";Extended Properties='Excel 12.0 Xml;HDR=YES'"
        Dim cn As New OleDbConnection(connectionstring)
        cn.Open()
        Dim strSql As String = "select *from[Sheet1$]"
        Dim ap As New OleDbDataAdapter(strSql, cn)
        ap.Fill(tableExcel)
        dgv.DataSource = tableExcel
    End Sub

    Private Sub btnDuongDan_Click_1(sender As System.Object, e As System.EventArgs) Handles btnDuongDan.Click
        Dim save As New OpenFileDialog
        save.Title = "Chọn nơi sao lưu dữ liệu"
        save.Filter = "*.xlsx|*.xlsx|*.xls|*.xls"
        save.ShowDialog()
        txtDuongDan.Text = save.FileName
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If txtDuongDan.Text <> "" Then
            ImportsExcel(txtDuongDan.Text, dgvBDS)
            For i As Integer = 0 To dgvBDS.Rows.Count - 1
                Dim nhaDTO As New DangTinDTO
                Dim dcDTO As New DiaChiDTO
                dcDTO.SoNha = dgvBDS.Rows(i).Cells(3).Value.ToString()
                dcDTO.Duong = dgvBDS.Rows(i).Cells(4).Value.ToString()
                dcDTO.Phuong = dgvBDS.Rows(i).Cells(5).Value.ToString()
                dcDTO.Quan = dgvBDS.Rows(i).Cells(6).Value.ToString()
                listDanhSachDiaChi.Add(dcDTO)
                '------------------------------------------------------------------------
                nhaDTO.TieuDe = dgvBDS.Rows(i).Cells(0).Value.ToString()
                nhaDTO.MaLoaiNha = dgvBDS.Rows(i).Cells("MaLoaiNha").Value.ToString()
                nhaDTO.MaChuNha = dgvBDS.Rows(i).Cells("MaChuNha").Value.ToString()
                nhaDTO.MaLoaiTin = 1
                nhaDTO.GiaThue = Convert.ToInt32(dgvBDS.Rows(i).Cells(7).Value.ToString())
                nhaDTO.DienTich = Convert.ToInt32(dgvBDS.Rows(i).Cells(8).Value.ToString())
                nhaDTO.SucChua = Convert.ToInt32(dgvBDS.Rows(i).Cells(9).Value.ToString())
                nhaDTO.TinhTrang = Convert.ToInt32(dgvBDS.Rows(i).Cells(10).Value.ToString())
                nhaDTO.OGhep = Convert.ToInt32(dgvBDS.Rows(i).Cells(11).Value.ToString())
                nhaDTO.OChungChu = Convert.ToInt32(dgvBDS.Rows(i).Cells(12).Value.ToString())
                nhaDTO.GanCho = Convert.ToInt32(dgvBDS.Rows(i).Cells(13).Value.ToString())
                nhaDTO.GanTramXeBuyt = Convert.ToInt32(dgvBDS.Rows(i).Cells(14).Value.ToString())
                nhaDTO.ChoNuThue = Convert.ToInt32(dgvBDS.Rows(i).Cells(15).Value.ToString())
                nhaDTO.ChoNamThue = Convert.ToInt32(dgvBDS.Rows(i).Cells(16).Value.ToString())
                nhaDTO.ChoNauAn = Convert.ToInt32(dgvBDS.Rows(i).Cells(17).Value.ToString())
                nhaDTO.Internet = Convert.ToInt32(dgvBDS.Rows(i).Cells(18).Value.ToString())
                nhaDTO.TruyenHinhCap = Convert.ToInt32(dgvBDS.Rows(i).Cells(19).Value.ToString())
                nhaDTO.ThoiGianDangTin = "..."
                nhaDTO.ThoiGianDongTin = "..."
                listDanhSachNha.Add(nhaDTO)
            Next
            dgvBDS.DataSource = listDanhSachNha
        Else
            MessageBox.Show("Vui lòng chọn đường dẫn", "Thông báo")
        End If
    End Sub
    Public listDanhSachNha As New List(Of DangTinDTO)
    Public listDanhSachDiaChi As New List(Of DiaChiDTO)
    Public listDanhSachSinhVien As New List(Of SinhVienDTO)
    Dim nhaDTO As New DangTinDTO
    Dim nhaBUS As New DangTinBUS
    Dim diachiDTO As New DiaChiDTO
    Dim diachiBUS As New DiaChiBUS
    Dim tableNha As New DataTable

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        For i As Integer = 0 To listDanhSachNha.Count - 1

            '-----------------------------------------------------------------
            diachiDTO.Duong = listDanhSachDiaChi.Item(i).Duong
            diachiDTO.Phuong = listDanhSachDiaChi.Item(i).Phuong
            diachiDTO.Quan = listDanhSachDiaChi.Item(i).Quan
            diachiDTO.SoNha = listDanhSachDiaChi.Item(i).SoNha
            diachiDTO.MaDiaChi = 1
            diachiBUS.Insert(diachiDTO)
            diachiDTO = diachiBUS.GetItemID(diachiDTO.SoNha, diachiDTO.Duong, diachiDTO.Phuong, diachiDTO.Quan)
            '---------------------------------------------------------------------------
            nhaDTO.MaLoaiNha = listDanhSachNha.Item(i).MaLoaiNha
            nhaDTO.MaLoaiTin = listDanhSachNha.Item(i).MaLoaiTin
            nhaDTO.MaChuNha = listDanhSachNha.Item(i).MaChuNha
            nhaDTO.TieuDe = listDanhSachNha.Item(i).TieuDe
            nhaDTO.MaDangTin = listDanhSachNha.Item(i).MaDangTin
            '--------------------------------------------------------
            nhaDTO.DienTich = listDanhSachNha.Item(i).DienTich
            nhaDTO.GiaThue = listDanhSachNha.Item(i).GiaThue
            nhaDTO.SucChua = listDanhSachNha.Item(i).SucChua
            '--------------------------------------------------------
            nhaDTO.OGhep = listDanhSachNha.Item(i).OGhep
            nhaDTO.OChungChu = listDanhSachNha.Item(i).OChungChu
            nhaDTO.GanCho = listDanhSachNha.Item(i).GanCho
            nhaDTO.GanTramXeBuyt = listDanhSachNha.Item(i).GanTramXeBuyt
            nhaDTO.ChoNuThue = listDanhSachNha.Item(i).ChoNuThue
            nhaDTO.ChoNamThue = listDanhSachNha.Item(i).ChoNamThue
            nhaDTO.ChoNauAn = listDanhSachNha.Item(i).ChoNauAn
            nhaDTO.Internet = listDanhSachNha.Item(i).Internet
            nhaDTO.TruyenHinhCap = listDanhSachNha.Item(i).TruyenHinhCap
            '--------------------------------------------------------
            nhaDTO.ThoiGianDangTin = ".."
            nhaDTO.ThoiGianDongTin = ".."
            nhaDTO.MaDiaChi = diachiDTO.MaDiaChi
            nhaDTO.TinhTrang = 1
            nhaDTO.MaDangTin = 1
            nhaBUS.Insert(nhaDTO)
        Next
        MessageBox.Show("Thành công", "Thông báo")
    End Sub

    Private Sub btnDuongDanKH_Click(sender As System.Object, e As System.EventArgs) Handles btnDuongDanKH.Click
        Dim save As New OpenFileDialog
        save.Title = "Chọn nơi sao lưu dữ liệu"
        save.Filter = "*.xlsx|*.xlsx|*.xls|*.xls"
        save.ShowDialog()
        txtDuongDanKH.Text = save.FileName
    End Sub

    Private Sub btnImportKH_Click(sender As System.Object, e As System.EventArgs) Handles btnImportKH.Click
        If txtDuongDanKH.Text <> "" Then
            ImportsExcel(txtDuongDanKH.Text, dgvSV)
            For i As Integer = 0 To dgvSV.Rows.Count - 2
                '------------------------------------------------------------------------
                Dim svDTO As New SinhVienDTO
                svDTO.MaSinhVien = 1
                svDTO.MaTaiKhoan = 1
                svDTO.HoTen = dgvSV.Rows(i).Cells(2).Value.ToString()
                svDTO.Email = dgvSV.Rows(i).Cells(4).Value.ToString()
                svDTO.DienThoai = "0" + dgvSV.Rows(i).Cells(3).Value.ToString()
                svDTO.CMND = dgvSV.Rows(i).Cells(5).Value.ToString()
                svDTO.Truong = dgvSV.Rows(i).Cells(6).Value.ToString()
                listDanhSachSinhVien.Add(svDTO)
            Next
            dgvSV.DataSource = listDanhSachSinhVien
        Else
            MessageBox.Show("Vui lòng chọn đường dẫn", "Thông báo")
        End If
    End Sub

    Private Sub btnLuuKH_Click(sender As System.Object, e As System.EventArgs) Handles btnLuuKH.Click
        Dim iThanhCong As Integer = 0
        Dim iThatBai As Integer = 0
        For i As Integer = 0 To listDanhSachSinhVien.Count - 1
            Dim svDTO As New SinhVienDTO
            Dim svBUS As New SinhVienBUS
            svDTO.CMND = listDanhSachSinhVien.Item(i).CMND
            svDTO.DienThoai = listDanhSachSinhVien.Item(i).DienThoai
            svDTO.Email = listDanhSachSinhVien.Item(i).Email
            svDTO.HoTen = listDanhSachSinhVien.Item(i).HoTen
            svDTO.Truong = listDanhSachSinhVien.Item(i).Truong
            svDTO.MaTaiKhoan = listDanhSachSinhVien.Item(i).MaTaiKhoan
            svDTO.MaSinhVien = listDanhSachSinhVien.Item(i).MaSinhVien
            If svBUS.Insert(svDTO) = True Then
                iThanhCong = iThanhCong + 1
            Else
                iThatBai = iThatBai + 1
            End If
        Next
        Dim str As String = "Đã thêm " + iThanhCong.ToString() + " record"
        MessageBox.Show(str, "Thông báo")
    End Sub
End Class